{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# §9 Getting Data\n",
    "\n",
    "In order to be a data scientist you need data. In fact, you will spend an embarrassingly large fraction of your time acquiring, cleaning, and transforming data. In this chapter, we'll look at different ways of getting data into Python and into the right formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.1 Data IO\n",
    "\n",
    "### 9.1.1 CSV\n",
    "\n",
    "Comma-Separated Value (CSV) format is the most common import and export format for spreadsheets and databases.\n",
    "\n",
    "The csv module’s reader and writer objects read and write sequences. Programmers can also read and write data in dictionary form using the DictReader and DictWriter classes.\n",
    "\n",
    "Reference:\n",
    "\n",
    "https://docs.python.org/3/library/csv.html\n",
    "\n",
    "csv.writer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "header_list = ['name', 'age', 'score']\n",
    "data_list = [['Sai',35,100], ['Wang',30,90], ['Li',25,80]]\n",
    "\n",
    "f = open('csv_test.csv', mode='w', encoding='utf-8-sig', newline='')\n",
    "writer = csv.writer(f) #delimiter=','\n",
    "writer.writerow(header_list)\n",
    "writer.writerows(data_list)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "csv.reader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['name', 'age', 'score']\n",
      "['Sai', '35', '100']\n",
      "['Wang', '30', '90']\n",
      "['Li', '25', '80']\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "f = open('csv_test.csv', mode='r', encoding='utf-8-sig')\n",
    "reader = csv.reader(f)\n",
    "for row in reader:\n",
    "    print(row)\n",
    "    \n",
    "print(reader.line_num)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "csv.DictWriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "header_list = ['name', 'age', 'score']\n",
    "data_list = [{'name':'Sai','age':35,'score':100}, \n",
    "             {'name':'Wang','age':35,'score':90},\n",
    "             {'name':'Li','age':35,'score':80},\n",
    "             {'name':'Liu','age':20,'score':85}]\n",
    "\n",
    "f = open('csv_dict_test.csv', mode='w', encoding='utf-8-sig', newline='')\n",
    "writer = csv.DictWriter(f, header_list)\n",
    "writer.writeheader()\n",
    "writer.writerows(data_list)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "csv.DictReader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('name', 'Sai'), ('age', '35'), ('score', '100')])\n",
      "OrderedDict([('name', 'Wang'), ('age', '35'), ('score', '90')])\n",
      "OrderedDict([('name', 'Li'), ('age', '35'), ('score', '80')])\n",
      "OrderedDict([('name', 'Liu'), ('age', '20'), ('score', '85')])\n"
     ]
    }
   ],
   "source": [
    "f = open('csv_dict_test.csv', mode='r', encoding='utf-8-sig')\n",
    "reader = csv.DictReader(f)\n",
    "for row in reader:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.1.2 HDF5\n",
    "\n",
    "Hierarchical Data Format 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/\n",
      "/tutors\n"
     ]
    }
   ],
   "source": [
    "import h5py\n",
    "import numpy as np\n",
    "\n",
    "f = h5py.File('data.h5', 'w') \n",
    "print(f.name) #f is file handle and root group\n",
    "t = f.create_group('tutors')\n",
    "print(t.name) #group name\n",
    "student1 = t.create_group('Sai/Huang')\n",
    "student2 = t.create_group('Sai/Xia')\n",
    "student1['score'] = np.array([80,90,100]) #store dataset\n",
    "student2['score'] = np.array([90,90,90])\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['tutors']\n",
      "tutors\n",
      "tutors/Sai\n",
      "tutors/Sai/Huang\n",
      "tutors/Sai/Huang/score\n",
      "tutors/Sai/Xia\n",
      "tutors/Sai/Xia/score\n",
      "KeysView(<HDF5 group \"/tutors/Sai\" (2 members)>)\n",
      "[90 90 90]\n",
      "[ 80  90 100]\n",
      "True\n",
      "2022\n"
     ]
    }
   ],
   "source": [
    "f = h5py.File('data.h5', 'r+')\n",
    "print(list(f.keys()))\n",
    "f.visit(lambda x: print(x))\n",
    "t = f['tutors/Sai'] #like directory\n",
    "print(t.keys())\n",
    "\n",
    "s = t['Xia']\n",
    "data = s['score']\n",
    "print(data.value) #access data\n",
    "s2 = t['Huang']\n",
    "print(s2['score'][()]) #same with .value\n",
    "\n",
    "#add attributes for group and dataset\n",
    "data.attrs['year'] = 2022\n",
    "data.attrs['subject'] = 'math physics computer'\n",
    "print('year' in data.attrs)\n",
    "print(data.attrs['year'])\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.1.3 JSON\n",
    "\n",
    "JavaScript Object Notation (JSON) is a human readable lightweight plain text format.\n",
    "\n",
    "Python supports JSON natively. \n",
    "\n",
    "JSON can store int, float, string, bool, None, list and dict type of python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\"string\", 3.14, 2, null, {\"black\": 0, \"white\": 255}]\n",
      "['string', 3.14, 2, None, {'black': 0, 'white': 255}]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "data = ['string', 3.14, 2, None, {'black':0, 'white':255}]\n",
    "data_json = json.dumps(data) #python data to json\n",
    "print(data_json)\n",
    "\n",
    "with open('data.json', 'w') as f:\n",
    "    json.dump(data, f)\n",
    "    \n",
    "with open('data.json', 'r') as f:\n",
    "    data_from_file = json.load(f)\n",
    "print(data_from_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.1.4 excel\n",
    "\n",
    "There are several modules can read and write excel, such as xlrd (xlwt), xlwings, openpyxl and pandas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.2 pandas\n",
    "    \n",
    "Pandas generally provide two data structures for manipulating data, They are: \n",
    "\n",
    "    Series\n",
    "    DataFrame\n",
    "\n",
    "pandas are used in conjunction with other libraries that are used for data science. It is built on the top of the NumPy library which means that a lot of structures of NumPy are used or replicated in Pandas. The data produced by Pandas are often used as input for plotting functions of Matplotlib, statistical analysis in SciPy, and machine learning algorithms in Scikit-learn.\n",
    "\n",
    "Advantages: \n",
    "\n",
    "    Fast and efficient for manipulating and analyzing data.\n",
    "    Data from different file objects can be loaded.\n",
    "    Easy handling of missing data (represented as NaN) in floating point as well as non-floating point data\n",
    "    Size mutability: columns can be inserted and deleted from DataFrame and higher dimensional objects\n",
    "    Data set merging and joining.\n",
    "    Flexible reshaping and pivoting of data sets\n",
    "    Provides time-series functionality.\n",
    "    Powerful group by functionality for performing split-apply-combine operations on data sets.\n",
    "\n",
    "https://pandas.pydata.org/docs/user_guide/index.html\n",
    "\n",
    "### 9.2.1 Series\n",
    "\n",
    "Pandas Series is a one-dimensional labeled array capable of holding data of any type (integer, string, float, python objects, etc.). The axis labels are collectively called indexes. Pandas Series is nothing but a column in an excel sheet. Labels need not be unique but must be a hashable type. The object supports both integer and label-based indexing and provides a host of methods for performing operations involving the index.\n",
    "\n",
    "pandas.Series(data, index, dtype, name, copy=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series([], dtype: float64)\n",
      "0    5\n",
      "1    7\n",
      "2    6\n",
      "dtype: int64\n",
      "5 7\n",
      "a     sai\n",
      "b    wang\n",
      "c      li\n",
      "dtype: object\n",
      "sai\n",
      "a     sai\n",
      "b    wang\n",
      "c      li\n",
      "Name: create from dict test, dtype: object\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "Cannot interpret '<attribute 'dtype' of 'numpy.generic' objects>' as a data type",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-bf1f3d3dfde8>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     24\u001b[0m \u001b[1;31m#creating a series from Scalar value\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     25\u001b[0m \u001b[1;31m#the scalar value will be repeated to match the length of the index\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 26\u001b[1;33m \u001b[0ms5\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mSeries\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m10\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m5\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m6\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m7\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     27\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms5\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, data, index, dtype, name, copy, fastpath)\u001b[0m\n\u001b[0;32m    273\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    274\u001b[0m                 data = _sanitize_array(data, index, dtype, copy,\n\u001b[1;32m--> 275\u001b[1;33m                                        raise_cast_failure=True)\n\u001b[0m\u001b[0;32m    276\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    277\u001b[0m                 \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSingleBlockManager\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfastpath\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m_sanitize_array\u001b[1;34m(data, index, dtype, copy, raise_cast_failure)\u001b[0m\n\u001b[0;32m   4147\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4148\u001b[0m             subarr = construct_1d_arraylike_from_scalar(\n\u001b[1;32m-> 4149\u001b[1;33m                 value, len(index), dtype)\n\u001b[0m\u001b[0;32m   4150\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4151\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py\u001b[0m in \u001b[0;36mconstruct_1d_arraylike_from_scalar\u001b[1;34m(value, length, dtype)\u001b[0m\n\u001b[0;32m   1199\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mis_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1200\u001b[0m             \u001b[0mdtype\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfloat64\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1201\u001b[1;33m         \u001b[0msubarr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlength\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1202\u001b[0m         \u001b[0msubarr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfill\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1203\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: Cannot interpret '<attribute 'dtype' of 'numpy.generic' objects>' as a data type"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "#create an empty series\n",
    "s1 = pd.Series()\n",
    "print(s1)\n",
    "\n",
    "#create series form a list\n",
    "#default index is 0,1,2,...\n",
    "s2 = pd.Series([5,7,6])\n",
    "print(s2)\n",
    "print(s2[0],s2[1])\n",
    "\n",
    "#providing an index\n",
    "s3 = pd.Series(['sai','wang','li'], ['a','b','c'])\n",
    "print(s3)\n",
    "print(s3['a'])\n",
    "\n",
    "#create series from dictionary\n",
    "s4 = pd.Series({'a':'sai', 'b':'wang', 'c':'li'}, name='create from dict test')\n",
    "print(s4)\n",
    "\n",
    "#creating a series from Scalar value\n",
    "#the scalar value will be repeated to match the length of the index\n",
    "s5 = pd.Series(10, [4,5,6,7])\n",
    "print(s5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2.2 DataFrame\n",
    "\n",
    "Pandas DataFrame is a two-dimensional size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns). A Data frame is a two-dimensional data structure, i.e., data is aligned in a tabular fashion in rows and columns. Pandas DataFrame consists of three principal components, the data, rows, and columns.\n",
    "\n",
    "pandas.DataFrame(data, index, columns, dtype, copy=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: []\n",
      "Index: []\n",
      "      0\n",
      "0   sai\n",
      "1  wang\n",
      "2    li\n",
      "   name   age\n",
      "0   sai  35.0\n",
      "1  wang  30.0\n",
      "2    li  25.0\n",
      "    name  age  score\n",
      "p1   sai   35    100\n",
      "p2  wang   30     80\n",
      "p3    li   25     90\n",
      "    age  name\n",
      "0  35.0   sai\n",
      "1  30.0  wang\n",
      "2   NaN    li\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame()\n",
    "print(df1)\n",
    "\n",
    "#1 column, default column name is 0,1,..., default row name is 0,1,...\n",
    "data1 = ['sai', 'wang', 'li']\n",
    "df2 = pd.DataFrame(data1)\n",
    "print(df2)\n",
    "\n",
    "data2 = [['sai',35], ['wang',30], ['li',25]]\n",
    "df3 = pd.DataFrame(data2, columns=['name','age'], dtype=float)\n",
    "print(df3)\n",
    "\n",
    "#create by column\n",
    "data3 = {'name':['sai','wang','li'], 'age':[35,30,25], 'score':[100,80,90]}\n",
    "df4 = pd.DataFrame(data3, dtype=int, index=['p1','p2','p3'])\n",
    "print(df4)\n",
    "\n",
    "#create by row\n",
    "data4 = [{'name':'sai','age':35}, {'name':'wang','age':30}, {'name':'li'}]\n",
    "df5 = pd.DataFrame(data4, dtype=float)\n",
    "print(df5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2.3 view data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name  age  score\n",
      "p1   sai   35    100\n",
      "p2  wang   30     80\n",
      "    name  age  score\n",
      "p2  wang   30     80\n",
      "p3    li   25     90\n",
      "\n",
      "basic infomation of DataFrame df4:\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "Cannot interpret '<attribute 'dtype' of 'numpy.generic' objects>' as a data type",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-11-99f455014258>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'\\nbasic infomation of DataFrame df4:'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf4\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minfo\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'dimention:'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf4\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'element number:'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf4\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msize\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36minfo\u001b[1;34m(self, verbose, buf, max_cols, memory_usage, null_counts)\u001b[0m\n\u001b[0;32m   2272\u001b[0m                         self.index._is_memory_usage_qualified()):\n\u001b[0;32m   2273\u001b[0m                     \u001b[0msize_qualifier\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m'+'\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2274\u001b[1;33m             \u001b[0mmem_usage\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmemory_usage\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdeep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdeep\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2275\u001b[0m             lines.append(\"memory usage: {mem}\\n\".format(\n\u001b[0;32m   2276\u001b[0m                 mem=_sizeof_fmt(mem_usage, size_qualifier)))\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mmemory_usage\u001b[1;34m(self, index, deep)\u001b[0m\n\u001b[0;32m   2366\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2367\u001b[0m             result = Series(self.index.memory_usage(deep=deep),\n\u001b[1;32m-> 2368\u001b[1;33m                             index=['Index']).append(result)\n\u001b[0m\u001b[0;32m   2369\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2370\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, data, index, dtype, name, copy, fastpath)\u001b[0m\n\u001b[0;32m    273\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    274\u001b[0m                 data = _sanitize_array(data, index, dtype, copy,\n\u001b[1;32m--> 275\u001b[1;33m                                        raise_cast_failure=True)\n\u001b[0m\u001b[0;32m    276\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    277\u001b[0m                 \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSingleBlockManager\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfastpath\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m_sanitize_array\u001b[1;34m(data, index, dtype, copy, raise_cast_failure)\u001b[0m\n\u001b[0;32m   4147\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4148\u001b[0m             subarr = construct_1d_arraylike_from_scalar(\n\u001b[1;32m-> 4149\u001b[1;33m                 value, len(index), dtype)\n\u001b[0m\u001b[0;32m   4150\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4151\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py\u001b[0m in \u001b[0;36mconstruct_1d_arraylike_from_scalar\u001b[1;34m(value, length, dtype)\u001b[0m\n\u001b[0;32m   1199\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mis_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1200\u001b[0m             \u001b[0mdtype\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfloat64\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1201\u001b[1;33m         \u001b[0msubarr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlength\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1202\u001b[0m         \u001b[0msubarr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfill\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1203\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: Cannot interpret '<attribute 'dtype' of 'numpy.generic' objects>' as a data type"
     ]
    }
   ],
   "source": [
    "print(df4.head(2)) #view top n data, default n is 5\n",
    "print(df4.tail(2)) #view tail n data, default n is 5\n",
    "\n",
    "print('\\nbasic infomation of DataFrame df4:')\n",
    "print(df4.info())\n",
    "print('dimention:', df4.ndim)\n",
    "print('element number:', df4.size)\n",
    "print('shape',df4.shape)\n",
    "\n",
    "print('\\nprint 1 row:\\n', df4.loc['p1'])\n",
    "print('\\nprint multi rows:\\n', df4.loc[['p1','p3']])\n",
    "\n",
    "print('\\n colunm names:', df4.columns)\n",
    "print('\\n print 1 colunm:\\n', df4['age'])\n",
    "print(type(df4['age']))\n",
    "print('\\n print multi colunm:\\n', df4[['age','score']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2.4 Manipulating data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'to_numpy'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-12-ee7e7f7ee348>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf4\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'age'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'score'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_numpy\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf4\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m#adding new column\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mdf4\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'country'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;34m'China'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Pakistan'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Bangladesh'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[1;34m(self, name)\u001b[0m\n\u001b[0;32m   4374\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4375\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4376\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4377\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4378\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'to_numpy'"
     ]
    }
   ],
   "source": [
    "print(df4[['age', 'score']].to_numpy())\n",
    "print(df4)\n",
    "\n",
    "#adding new column\n",
    "df4['country'] = ['China', 'Pakistan', 'Bangladesh']\n",
    "print('Add column country:\\n', df4)\n",
    "\n",
    "##adding new column at the given place\n",
    "df4.insert(2, 'gender', ['M','F','M']) #adding to column 2\n",
    "print('\\nAdd column gender:\\n', df4)\n",
    "\n",
    "del df4['gender']\n",
    "df4.pop('country')\n",
    "print('\\nDel column gender, country:\\n', df4)\n",
    "\n",
    "#adding new rows\n",
    "df6 = pd.DataFrame([['liu',20,90], ['ma',25,80]], \n",
    "                   columns=['name', 'age', 'score'],\n",
    "                   index=['p4','p3'])\n",
    "df4 = df4.append(df6)\n",
    "print('\\nAdd row:\\n', df4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>p1</th>\n",
       "      <td>sai</td>\n",
       "      <td>35</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>p3</th>\n",
       "      <td>li</td>\n",
       "      <td>25</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  age  score\n",
       "p1  sai   35    100\n",
       "p3   li   25     90"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.drop('p2',inplace=True)\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   name age score\n",
      "p1  sai  35   100\n"
     ]
    }
   ],
   "source": [
    "df4.loc['p2']\n",
    "data = pd.DataFrame(columns=['name','age','score'],dtype='object')\n",
    "data=data.append(df4.loc['p1'])\n",
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in df4.iterrows():\n",
    "    print(i)\n",
    "    print(row)\n",
    "print()\n",
    "\n",
    "for key, value in df4.iteritems():\n",
    "    print(key)\n",
    "    print(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.sort_values('score', axis=0, ascending=True, inplace=True)\n",
    "print(df4)\n",
    "\n",
    "print(df5.dropna())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "statistics function:\n",
    "\n",
    "sum, mean, std, median, mode, min, max, corr, count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('sum:\\n', df4.sum())\n",
    "print('\\nsum along row:\\n', df4.sum(axis=1))\n",
    "print('\\ndescribe:\\n', df4['score'].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2.5 file IO\n",
    "csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.to_csv('pandas_csv.csv')\n",
    "df6 = pd.read_csv('pandas_csv.csv')\n",
    "print(df6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "JSON\n",
    "\n",
    "TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "df4.to_excel('pandas_excel.xlsx')\n",
    "df7 = pd.read_excel('pandas_excel.xlsx')\n",
    "print(df7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sqlite3\n",
    "\n",
    "TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2.6 visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "ts = pd.Series(np.random.randn(100), \n",
    "               index=pd.date_range(\"1/1/2000\", periods=100))\n",
    "ts = ts.cumsum()\n",
    "ts.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8 = pd.DataFrame(np.random.randn(100, 4), \n",
    "                   index=ts.index, columns=list(\"ABCD\"))\n",
    "df8 = df8.cumsum()\n",
    "print(df8.head)\n",
    "df8.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Other plot function:\n",
    "\n",
    "    ‘bar’ or ‘barh’ for bar plots\n",
    "    ‘hist’ for histogram\n",
    "    ‘box’ for boxplot\n",
    "    ‘kde’ or ‘density’ for density plots\n",
    "    ‘area’ for area plots\n",
    "    ‘scatter’ for scatter plots\n",
    "    ‘hexbin’ for hexagonal bin plots\n",
    "    ‘pie’ for pie plots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8['B'].plot.hist(bins=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Homework\n",
    "\n",
    "1. Read the sepals and petal lengths of iris flowers in the Iris dataset, sort them, remove duplicates, and calculate the sum, cumulative sum, mean, standard deviation, variance, maximum, and minimum values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
